Walkthrough 4-2: Connect to data (MySQL database)
In this walkthrough, you connect to a database and retrieve data from a table that contains flight information. You will:
· Add a Database Select operation.
· Configure a Database connector that connects to a MySQL database (or optionally an in-memory Derby database if you do not have access to port 3306).
· Configure the Database Select operation to use that Database connector.
· Write a query to select data from a table in the database.
_files/image226.png)
_files/image227.png)
Starting file
If you did not complete the previous walkthrough, you can get a starting file here. This file is also located in the solutions folder of the student files ZIP located in the Course Resources.
Locate database information
1. Return to the course snippets.txt file and locate the MySQL and Derby database information.
_files/image228.png)
Note: The database information you see may be different than what is shown here; the values in the snippets file differ for instructor-led and self-study training classes.
Add a Database connector endpoint
2. Return to Anypoint Studio.
3. Right-click the Set Payload message processor and select Delete.
4. In the Mule Palette, select Add Modules.
_files/image229.png)
5. Select the Database connector in the right side of the Mule Palette and drag and drop it into the left side.
_files/image230.png)
6. If you get a Select module version dialog box, select the latest version and click Add.
_files/image231.png)
7. Locate the new Database connector in the Mule Palette.
_files/image232.png)
8. Drag and drop the Select operation in the process section of the flow.
_files/image233.png)
Option 1: Configure a MySQL Database connector (if you have access to port 3306)
9. In the Select properties view, click the Add button next to connector configuration.
_files/image234.png)
10. In the Global Element Properties dialog box, set the Connection to MySQL Connection.
_files/image235.png)
11. Set the host, port, user, password, and database values to the values listed in the course snippets.txt file.
_files/image236.png)
12. Click the Configure button next to MySQL JDBC Driver.
13. In the configure drop-down menu, select Add Maven dependency.
_files/image237.png)
14. In the Maven dependency dialog box, locate the Search Maven Central text field.
_files/image238.png)
15. Enter mysql- in the Search Maven Central text field.
16. Select mysql:mysql-connector-java in the results that are displayed.
_files/image239.png)
17. Click Edit selected.
18. Click Finish.
_files/image240.png)
19. Back in the Global Element Properties dialog box, click the Test Connection button; you should get a successful test dialog box.
Note: Make sure the connection succeeds before proceeding.
_files/image241.png)
Note: If the connectivity test fails, make sure you are not behind a firewall restricting access to port 3306. If you cannot access port 3306, use the instructions in the next section for option 2.
20. Click OK to close the Test connection dialog box.
21. Click OK to close the Global Element Properties dialog box and proceed to the Write a query to return all flights section below.
Option 2: Configure a Derby Database connector (if no access to port 3306)
22. In a command-line interface, use the cd command to navigate to the folder containing the jars folder of the student files.
23. Run the mulesoft-training-services.jar file.
java –jar mulesoft-training-services-X.X.X.jar
Note: Replace X.X.X with the version of the JAR file, for example 1.8.3.
Note: The application uses ports 1527, 9090, 9091, and 61616. If any of these ports are already in use, you can change them when you start the application as shown in the following code.
java -jar mulesoft-training-services-X.X.X.jar --database.port=1530 --ws.port=9092 --activemq.broker.url=tcp://localhost:61617 --server.port=9193
24. Look at the output and make sure all the services started.
_files/image242.png)
Note: When you want to stop the application, return to this window and press Ctrl+C.
25. Return to Anypoint Studio.
26. In the Select properties view, click the Add button next to connector configuration.
27. In the Global Element Properties dialog box, set the Connection to Generic Connection.
_files/image243.png)
28. Set the URL and driver class name values to the values listed in the course snippets.txt file.
_files/image244.png)
29. Click the Configure button next to JDBC Driver.
30. In the configure drop-down menu, select Add Maven dependency.
31. In the Maven dependency dialog box, locate the Search Maven Central text field.
_files/image245.png)
32. Enter derbyclient in the Search Maven Central text field.
33. Select org.apache.derby:derbyclient in the results that are displayed.
_files/image246.png)
34. Click Edit selected.
35. Enter 10.14.2.0 in the Version text field.
Note: Version 10.14.2.0 is the latest Derby client compatible with Java 8.
_files/image247.png)
36. Click Finish.
37. Back in the Global Element Properties dialog box, click the Test Connection button; you should get a successful test dialog box.
Note: Make sure the connection succeeds before proceeding.
_files/image248.png)
38. Click OK to close the Test connection dialog box.
39. Click OK to close the Global Element Properties dialog box.
Write a query to return all flights
40. In the Select properties view, add a query to select all records from the american table.
SELECT *
FROM american
Test the application
41. Run the project.
42. In the Save changes dialog box, click Save.
43. Watch the console and wait for the application to start.
44. Once the application has started, return to Advanced REST Client.
45. In Advanced REST Client, make another request to http://localhost:8081/flights; you should get a 500 Server Error with an invalid data message.
_files/image249.png)